package uf.audit.tax;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Types;
import java.util.Properties;

/**
 * 
 *
 */
public class App {

	private static Properties loadConfig(String configPath) {
		Properties prop = new Properties();
		FileInputStream inStream;
		try {
			inStream = new FileInputStream(configPath);
			prop.load(inStream);
			inStream.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return prop;
	}

	private static void string2File(String content, String path) throws IOException {
		FileOutputStream outStream = new FileOutputStream(path);
		byte[] buffer = content.getBytes("UTF-8");
		outStream.write(buffer);
		outStream.close();
	}

	private static String helpContent = "java uf.audit.tax.App properties-file\nproperties-file format:\njdbc=jdbc:oracle:thin:@10.2.100.15:1521:orcl\nuser=swsoftpt\npassword=swsoftpt\nsql=SELECT * FROM TAX_SYS_INDICATOR\nfile=test.sql";

	public static void main(String[] args) {
		if (args == null || args.length != 1) {
			System.out.println(helpContent);
			System.exit(0);
		}

		Properties prop = loadConfig(args[0]);
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		String jdbc = prop.getProperty("jdbc", "");
		String user = prop.getProperty("user", "");
		String password = prop.getProperty("password", "");
		String sql = prop.getProperty("sql", "");
		String file = prop.getProperty("file", "");
		String sequence = prop.getProperty("sequence", "");
		String seqField = null, seqReplace = null;
		int seqIndex = 0;

		if (!sequence.equals("")) {
			String[] ss = sequence.split(":");
			seqField = ss[0];
			seqReplace = ss[1];
		}

		StringBuilder contents = new StringBuilder();

		Connection conn = null;
		try {
			conn = DriverManager.getConnection(jdbc, user, password);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			PreparedStatement stmt = conn.prepareStatement(sql);
			StringBuilder command = new StringBuilder();
			ResultSet rset = stmt.executeQuery();
			ResultSetMetaData meta = rset.getMetaData();
			int columns = meta.getColumnCount();
			int[] columtypes = new int[columns];
			command.append("INSERT (");
			for (int i = 1; i <= columns; i++) {
				if (i > 1) {
					command.append(',');
				}
				String columnName = meta.getColumnName(i);
				if (seqField.equalsIgnoreCase(columnName)) {
					seqIndex = i;
				}
				command.append(columnName);
				columtypes[i - 1] = meta.getColumnType(i);
			}
			command.append(") VALUES (");
			int head = command.length();
			while (rset.next()) {
				for (int i = 1; i <= columns; i++) {
					if (i > 1) {
						command.append(',');
					}
					if (i == seqIndex && seqReplace != null) {
						command.append(seqReplace);
						continue;
					}
					Object value = rset.getObject(i);
					if (value == null) {
						command.append("NULL");
					} else {
						int currentType = columtypes[i - 1];
						switch (currentType) {
						case Types.VARCHAR:
						case Types.NCHAR:
						case Types.CLOB:
						case Types.CHAR:
						case Types.LONGVARCHAR:
						case Types.NCLOB:
						case Types.LONGNVARCHAR:
							String sv = rset.getString(i);
							command.append('\'');
							command.append(sv);
							command.append('\'');
							break;
						case Types.INTEGER:
						case Types.BIGINT:
						case Types.BIT:
						case Types.DECIMAL:
						case Types.NUMERIC:
							int iv = rset.getInt(i);
							command.append(String.valueOf(iv));
							break;
						case Types.BLOB:
							command.append("<BLOB>");
							break;
						case Types.DOUBLE:
							double dv = rset.getDouble(i);
							command.append(String.valueOf(dv));
							break;
						case Types.FLOAT:
							float fv = rset.getFloat(i);
							command.append(String.valueOf(fv));
							break;
						case Types.NULL:
							command.append("NULL");
							break;
						case Types.DATE:
							Date d = rset.getDate(i);
							String ld = d.toLocalDate().toString();
							command.append('\'');
							command.append(ld);
							command.append('\'');
							break;
						case Types.TIMESTAMP:
							Time t = rset.getTime(i);
							String tv = t.toString();
							command.append('\'');
							command.append(tv);
							command.append('\'');
							break;
						default:
							command.append('\'');
							command.append(value.toString());
							command.append('\'');
						}
					}
				}
				command.append(");\r\n");
				String line = command.toString();
				contents.append(line);
				System.out.println(line);
				command.setLength(head);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (file != null && !file.equals("")) {
			try {
				string2File(contents.toString(), file);
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		System.out.println("---------> All ok!");
	}
}
